package com.netcracker.training.automonic.dao.entities.oracle;

import static com.netcracker.training.automonic.dao.entities.oracle.SQLQueries.*;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;

import org.apache.log4j.Logger;

import com.netcracker.training.automonic.dao.entities.Object;
import com.netcracker.training.automonic.dao.entities.Site;
import com.netcracker.training.automonic.dao.factories.oracle.OracleDAOFactory;

/**
 * Concrete implementation for Oracle Database Express Edition 11g Release 2.
 * 
 * @author Vadim Gidulyan
 * 
 * @see Site
 */

public class OracleSite implements Site // Javadoc
{

	public static final Logger LOGGER = Logger.getLogger(OracleSite.class);
	
	private static OracleDAOFactory daoFactory = new OracleDAOFactory();
	private static Connection con = getConnection();
	
	private static Connection getConnection()
	{
		if (con == null)
			con = daoFactory.createConnection();
		return con;
	}
	
	public static Site createSite(String name)
	{
		Site site = null;
		
		try (Statement st = con.createStatement())
		{
			String sql = "SELECT seq_Sites.NEXTVAL FROM DUAL";
			
			ResultSet rs = st.executeQuery(sql);
			
			if (rs.next())
			{
				int id = rs.getInt(1);
				site = new OracleSite(id, name);
			}
			rs.close();
		}
		catch (SQLException e)
		{
			LOGGER.error(null, e);
			e.printStackTrace();
		}
		finally
		{
			if (LOGGER.isTraceEnabled())
				LOGGER.trace(site);
		}
		
		return site;
	}
	
	public static Site selectSiteById(int id)
	{
		Site site = null;
		try (Statement st = con.createStatement())
		{
			String sql = String.format(SELECT_SITE_BY_ID, id);
			
			ResultSet rs = st.executeQuery(sql);
			
			if (rs.next())
			{
				site = new OracleSite(id, rs.getString("name"));
			}
			rs.close();
		}
		catch (SQLException e)
		{
			LOGGER.error(null, e);
			e.printStackTrace();
		}
		finally
		{
			if (LOGGER.isTraceEnabled())
				LOGGER.trace(site);
		}
		
		return site;
	}
	
	public static Site selectSiteByName(String name)
	{
		Site site = null;
		try (Statement st = con.createStatement())
		{
			String sql = String.format(SELECT_SITE_BY_NAME, name);
			
			ResultSet rs = st.executeQuery(sql);
			
			if (rs.next())
			{
				site = new OracleSite(rs.getInt("site_id"), name);
			}
			rs.close();
		}
		catch (SQLException e)
		{
			LOGGER.error(null, e);
			e.printStackTrace();
		}
		finally
		{
			if (LOGGER.isTraceEnabled())
				LOGGER.trace(site);
		}
		
		return site;
	}
	
	public static List<Site> selectAllSites()
	{
		List<Site> sites = new LinkedList<Site>();
		
		try (Statement st = con.createStatement())
		{
			ResultSet rs = st.executeQuery(SELECT_ALL_SITES);
			
			while (rs.next())
			{
				Site site = new OracleSite(rs.getInt("site_id"),
				                           rs.getString("name"));
				sites.add(site);
			}
			rs.close();
		}
		catch (SQLException e)
		{
			LOGGER.error(null, e);
			e.printStackTrace();
		}
		finally
		{
			if (LOGGER.isTraceEnabled())
				for (Site site : sites)
					LOGGER.trace(site);
		}
		
		return sites;
	}
	
	private final int id;
	private String name = "";
	
	private boolean modified;
	
	// CONSTRUCTORS
	
	protected OracleSite(int id, String name)
	{
		this.id = id;
		setName(name);
		
		modified = false;
	}
	
	// ACCESSORS
	
	@Override
	public int getId()
	{
		return id;
	}
	
	@Override
	public String getName()
	{
		return name;
	}
	
	@Override
	public List<Object> getObjects()
	{
		return OracleObject.selectObjectsBySite(this);
	}
	
	@Override
	public void setName(String name)
	{
		if (this.name.hashCode() != name.hashCode())
			modified = true;
		name = name.replaceAll("'{1,}", "''");
		this.name = name;
	}
	
	// METHODS
	
	@Override
	public String toString()
	{
		return String.format("%d %s", id, name);
	}

	@Override
	public void save()
	{
		boolean siteExists = (selectSiteById(id) != null);
		
		if (siteExists)
		{
			if (modified)
			{
				String sql = String.format(
						"UPDATE Sites "
					  + "SET name = '%s' "
					  + "WHERE site_id = %d",
						name, id);
				
				try (Statement st = con.createStatement())
				{
					st.executeUpdate(sql);
				}
				catch (SQLException e)
				{
					LOGGER.error(null, e);
					e.printStackTrace();
				}
				finally
				{
					if (LOGGER.isTraceEnabled())
						LOGGER.trace(sql);
				}
			}
		}
		else
		{
			String sql = String.format(
					"INSERT INTO Sites "
				  + "	VALUES (%d, '%s')",
					id, name);
			
			try (Statement st = con.createStatement())
			{
				st.executeUpdate(sql);
			}
			catch (SQLException e)
			{
				LOGGER.error(null, e);
				e.printStackTrace();
			}
			finally
			{
				if (LOGGER.isTraceEnabled())
					LOGGER.trace(sql);
			}
		}
		
		modified = false;
		LOGGER.info("Saved site: " + this);
	}

}
